﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SysContext;
using DataAccessLayer;

namespace DataAccessLayer
{
    public class TransactionsTable
    {
        public static string AccountType;
        public static int UserID;    
        
        SysDataContext Data = new SysDataContext();
        TimeRuleTable TimeTable = new TimeRuleTable();
        AccountsTable AccountTable = new AccountsTable();
        TransactionReminderTable ReminderTable = new TransactionReminderTable();
        
        /// <summary>
        /// Lấy toàn bộ dữ liệu trong bảng Transactions.
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="AccountType"></param>
        /// <returns>List Transaction</returns>
        public List<Transaction> SelectTransAll(int UserId, string AccountType)
        {
            var p = (from T in Data.Transactions
                     join A in Data.Accounts on T.Id equals A.Id
                     where A.UserId == UserId && A.Type == AccountType
                     select T).ToList();
            return p.ToList();
        }
        
        /// <summary>
        /// Lấy dữ liệu từ bảng Transactions và Category.
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="AccountType"></param>
        /// <returns>List</returns>
        public Array SelectTrans(int UserId, string AccountType)
        {
            var p = (from U in Data.Users
                     join A in Data.Accounts on U.Id equals A.UserId
                     join T in Data.Transactions on A.Id equals T.AccountId
                     join C in Data.Categories on T.CategoryId equals C.Id
                     where A.UserId == UserId && A.Type == AccountType
                     orderby U.Id
                     select new
                     {
                         T.OccurredDate,
                         T.Description,
                         C.Name,
                         T.Payment,
                         T.Deposit,
                         T.Balance
                     }).ToArray();
            return p.ToArray();            
        }       
        /// <summary>
        /// Thêm dữ liệu vào bảng Transactions.
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="AccountType"></param>
        /// <param name="pay"></param>
        /// <param name="Tienthu"></param>
        /// <param name="Thoigan"></param>
        /// <param name="CategoriesId"></param>
        /// <param name="Lydo"></param>
        /// <param name="total"></param>
       
        public void InsertTransation(int UserId, string AccountType, double pay, int Tienthu, DateTime Thoigan, int CategoriesId, string Lydo, Double total)
        {
            Data.Inserttransaction(AccountTable.GetAccountId(UserId,AccountType), pay, Tienthu, Thoigan, CategoriesId, Lydo, total);
        }
        
        /// <summary>
        /// Lấy danh sách bảng Transactions.
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="AccountType"></param>
        /// <returns></returns>
        public List<Transaction> getListOccurredDate(int UserId, string AccountType)
        {
            var p = (from i in Data.Transactions
                     join A in Data.Accounts on i.AccountId equals A.Id
                     where i.AccountId == AccountTable.GetAccountId(UserId, AccountType)
                     select i).ToList();
            return p.ToList();                              
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="AccountType"></param>
        /// <returns></returns>
        public DateTime GetOccurredDate(int UserId, string AccountType)
        {           
            double ID = -1;
            try
            {
                ID = getListOccurredDate(UserId, AccountType).Max(p => p.Id);
                Transaction Trans = (from i in Data.Transactions
                                     where i.Id == ID
                                     select i).SingleOrDefault();
                return Trans.OccurredDate;
            }
            catch
            {
                return DateTime.Now;
            }
        }

        /// <summary>
        /// Lấy dữ liệu từ bảng view TransactionsView theo tháng
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="AccountType"></param>
        /// <returns></returns>
        public Array SelectTransViewMonth(int UserId, string AccountType, string input)
        {
            var Tras_View = (from T in Data.TransactionsViews
                             where T.Type == AccountType && T.UserId == UserId && T.Month == input
                             select new
                             {
                                 ID = T.Id,
                                 T.Namecg,
                                 T.Namec,
                                 T.Month,
                                 Balance = T.Balance == null ? 0 : T.Balance.Value,
                             }).ToArray();
            return Tras_View.ToArray();
        }

        /// <summary>
        /// Lấy dữ liệu từ bảng view TransactionsView theo năm và danh mục.
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="AccountType"></param>
        /// <param name="category"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public Array SelectTransViewCategory(int UserId, string AccountType, string category, string year)
        {
            var Tras_View = (from T in Data.TransactionsViews
                             where T.Type == AccountType && T.UserId == UserId && T.Namec == category && T.Year == year
                             select new
                             {
                                 ID = T.Id,
                                 T.Namecg,
                                 T.Month,
                                 T.Year,
                                 T.Namec,
                                 Balance = T.Balance == null ? 0 : T.Balance.Value,
                             }).ToArray();
            return Tras_View.ToArray();
        }

        /// <summary>
        /// Lấy số dư tài khoản cho trang chủ.
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="AccountType"></param>
        /// <returns></returns>
        public double? Balance(int UserId, string AccountType)
        {
            double? SumBalance=0;
            var p = from T in Data.Transactions
                    join A in Data.Accounts on T.AccountId equals A.Id
                    where T.AccountId == AccountTable.GetAccountId(UserId, AccountType)
                    select new
                    {
                        T.Balance
                    };
            foreach (var Rs in p)
            {
                SumBalance = SumBalance + Rs.Balance;
            }
            return SumBalance;
        }       
    }
}
